using System.Data; 
using System.Text.RegularExpressions;
using System.Xml;
using Chaotic.DataBase.DataAccess;
using Chaotic.DataBase.DataAccess.Entity;
using Microsoft.Data.SqlClient;

namespace Chaotic.DataBase.MSSQLAccess;

public class MsSqlDataAccess : AbstractDataAccess
{
	private SqlConnection m_DbConnection;

	private SqlTransaction? trans;

	public override DatabaseType DatabaseType => DatabaseType.MSSQLServer;

	public override IDbConnection DbConnection => m_DbConnection;
 
	public MsSqlDataAccess(string connectionString)
	{
		m_DbConnection = new SqlConnection(connectionString);
	}

	public override void Close()
	{
		base.Close();
		m_DbConnection = new SqlConnection(m_DbConnection.ConnectionString);
	}

	public override IDbTransaction? BeginTransaction()
	{
		trans = m_DbConnection.BeginTransaction();
		return trans;
	}

	public override int ExecuteNonQuery(CommandType commandType, string? commandText, QueryParameterCollection commandParameters, DataRow row, SqlStruct sqlStruct)
	{
		int tmpValue = 0;
		LogOutPut(delegate
		{
			SqlCommand sqlCommand = new SqlCommand();
			if (sqlStruct == null)
			{
				PrepareCommand(sqlCommand, commandType, commandText, commandParameters);
			}
			else
			{
				for (int i = 0; i < sqlStruct.ParamFields.Length; i++)
				{
					if (sqlStruct.ParamFields[i].ParamName.IndexOf('_') != 1)
					{
						commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName]);
					}
					else
					{
						commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName, DataRowVersion.Original]);
					}
				}
				PrepareCommand(sqlCommand, CommandType.Text, sqlStruct.SqlString, commandParameters);
			}
			tmpValue = sqlCommand.ExecuteNonQuery();
			sqlCommand.Parameters.Clear();
			return tmpValue;
		}, "ExecuteNonQuery", commandText, commandParameters);
		return tmpValue;
	}

	public override DataSet? ExecuteDataset(CommandType commandType, string? commandText, QueryParameterCollection commandParameters, DataSet? ds, string tableName)
	{
		LogOutPut(delegate
		{
			SqlCommand sqlCommand = new SqlCommand();
			PrepareCommand(sqlCommand, commandType, commandText, commandParameters);
			SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
			if (object.Equals(tableName, null) || tableName.Length < 1)
			{
				sqlDataAdapter.Fill(ds);
			}
			else
			{
				sqlDataAdapter.Fill(ds, tableName);
			}
			for (int i = 0; i < sqlCommand.Parameters.Count; i++)
			{
				if (sqlCommand.Parameters[i].Direction == ParameterDirection.Output)
				{
					commandParameters[i].Value = sqlCommand.Parameters[i].Value;
				}
			}
			sqlCommand.Parameters.Clear();
			return (ds != null && ds.Tables.Count > 0) ? ds.Tables[0].Rows.Count : 0;
		}, "ExecuteDataset", commandText, commandParameters);
		return ds;
	}

	public override DataSet? ExecuteDataset(string? commandText, QueryParameterCollection commandParameters, DataSet? ds, int startRecord, int maxRecords, string tableName)
	{
		LogOutPut(delegate
		{
			SqlCommand sqlCommand = new SqlCommand();
			PrepareCommand(sqlCommand, CommandType.Text, commandText, commandParameters);
			SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
			if (object.Equals(tableName, null) || tableName.Trim() == string.Empty)
			{
				sqlDataAdapter.Fill(ds);
			}
			else
			{
				sqlDataAdapter.Fill(ds, startRecord, maxRecords, tableName);
			}
			sqlCommand.Parameters.Clear();
			return (ds != null && ds.Tables.Count > 0) ? ds.Tables[0].Rows.Count : 0;
		}, "ExecuteDataset", commandText, commandParameters);
		return ds;
	}

	public override IDataReader ExecuteReader(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
	{
		IDataReader dr = null;
		LogOutPut(delegate
		{
			SqlCommand sqlCommand = new SqlCommand();
			PrepareCommand(sqlCommand, commandType, commandText, commandParameters);
			dr = sqlCommand.ExecuteReader();
			sqlCommand.Parameters.Clear();
			return -1;
		}, "ExecuteReader", commandText, commandParameters);
		return dr;
	}

	public override object? ExecuteScalar(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
	{
		object? tmpValue = null;
		LogOutPut(delegate
		{
			SqlCommand sqlCommand = new SqlCommand();
			PrepareCommand(sqlCommand, commandType, commandText, commandParameters);
			tmpValue = sqlCommand.ExecuteScalar();
			sqlCommand.Parameters.Clear();
			return -1;
		}, "ExecuteScalar", commandText, commandParameters);
		return tmpValue;
	}

	public override XmlReader ExecuteXmlReader(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
	{
		XmlReader reader = null;
		LogOutPut(delegate
		{
			SqlCommand sqlCommand = new SqlCommand();
			PrepareCommand(sqlCommand, commandType, commandText, commandParameters);
			reader = sqlCommand.ExecuteXmlReader();
			sqlCommand.Parameters.Clear();
			return -1;
		}, "ExecuteXmlReader", commandText, commandParameters);
		return reader;
	}

	private void PrepareCommand(SqlCommand cmd, CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
	{
		if (Regex.IsMatch(commandText, "'\\d{4}-(1[0-2]|[0]?\\d)-(3[01]|[0-2]?\\d)[-,\\s*](2[0-3]|[0-1]?\\d)[.,:]([0-5]\\d|[0]?\\d)[.,:]([0-5]\\d|[0]?\\d)'"))
		{
			foreach (Match item in Regex.Matches(commandText, "'\\d{4}-(1[0-2]|[0]?\\d)-(3[01]|[0-2]?\\d)[-,\\s*](2[0-3]|[0-1]?\\d)[.,:]([0-5]\\d|[0]?\\d)[.,:]([0-5]\\d|[0]?\\d)'"))
			{
				if (item.Value.IndexOf(':') > 0)
				{
					string newValue = item.Value.Replace(":", "##");
					commandText = commandText.Replace(item.Value, newValue);
				}
			}
		}
		commandText = commandText.Replace(":", "@");
		commandText = commandText.Replace("##", ":");
		cmd.CommandType = commandType;
		cmd.CommandText = commandText;
		cmd.Connection = m_DbConnection;
		cmd.Transaction = trans;
		DbTypeEnum dbTypeEnum = new DbTypeEnum();
		if (commandParameters == null || commandParameters.Count <= 0)
		{
			return;
		}
		for (int i = 0; i < commandParameters.Count; i++)
		{
			cmd.Parameters.AddWithValue(commandParameters[i].ParameterName, commandParameters[i].Value ?? DBNull.Value);
			cmd.Parameters[i].Direction = commandParameters[i].Direction;
			if (cmd.CommandType == CommandType.StoredProcedure)
			{
				cmd.Parameters[i].SqlDbType = (SqlDbType)commandParameters[i].DbType;
			}
			int typeEnum = dbTypeEnum.GetTypeEnum(cmd.Parameters[i].DbType.ToString());
			if (typeEnum != 0)
			{
				cmd.Parameters[0].SqlDbType = (SqlDbType)typeEnum;
			}
		}
	}

	public override void FillingTables(List<TableInfo>? info)
	{
		using IDataReader dataReader = ExecuteReader("select table_name as TABLENAME, PKNAME\r\n                                                from information_schema.tables a \r\n                                                LEFT JOIN (SELECT sysindexes.name as PKNAME,sysindexes.id FROM sysindexes,sysobjects \r\n                                                where \r\n                                                sysindexes.name=sysobjects.name   and xtype='PK') t on t.id  = object_id(a.table_name)\r\n                                                where a.table_name != 'sysdiagrams' and a.table_type = 'BASE TABLE' \r\n                                                order by table_name");
		while (dataReader.Read())
		{
			info.Add(new TableInfo
			{
				TableName = dataReader["TABLENAME"].ToString(),
				PrimaryKeyName = dataReader["PKNAME"].ToString()
			});
		}
	}

	public override void FillingColumns(List<ColumnInfo> info)
	{
		using IDataReader dataReader = ExecuteReader("SELECT a.Table_name as TABLENAME,b.COLUMN_NAME as COLUMNNAME,b.DATA_TYPE  as TYPENAME,case when b.CHARACTER_MAXIMUM_LENGTH is null then b.numeric_precision else b.CHARACTER_MAXIMUM_LENGTH  end LENGTH,\r\n                                         (\r\n                                              select   count(1)  from   syscolumns  \r\n                                              join   sysindexkeys on   syscolumns.id=sysindexkeys.id   and   syscolumns.colid=sysindexkeys.colid   \r\n                                              join   sysindexes  on   syscolumns.id=sysindexes.id   and   sysindexkeys.indid=sysindexes.indid  \r\n                                              join   sysobjects  on   sysindexes.name=sysobjects.name   and   sysobjects.xtype='PK'\r\n                                              WHERE syscolumns.name=b.COLUMN_NAME AND     syscolumns.id=object_id(a.Table_name )\r\n                                          ) PRIMARYKEY ,\r\n                                          (select count(1)\r\n                                              from syscolumns col,sysforeignkeys f\r\n                                              where f.fkeyid=col.id\r\n                                            and f.fkey=col.colid and col.name=b.COLUMN_NAME\r\n                                            and f.constid in\r\n                                                (select distinct(id)\r\n                                                 from sysobjects\r\n                                                 where xtype='F' and OBJECT_NAME(parent_obj)=a.Table_name)\r\n                                          ) FOREIGNKEY,\r\n                                          case b.IS_NULLABLE when 'NO' then 'Not Null' when 'YES' then 'Null' end ISNULL  ,\r\n                                          case when numeric_scale is null then 0 else numeric_scale end DATA_SCALE,ORDINAL_POSITION AS COLUMNINDEX,\r\n                                          COLUMN_DEFAULT as DEFAULTVALUE\r\n                                         FROM INFORMATION_SCHEMA.TABLES  a   \r\n                                         LEFT JOIN INFORMATION_SCHEMA.COLUMNS b    \r\n                                              ON a.TABLE_NAME = b.TABLE_NAME\r\n                                              order by a.TABLE_NAME,PRIMARYKEY desc,Foreignkey desc,COLUMN_NAME  ");
		while (dataReader.Read())
		{
			string typename = dataReader["TYPENAME"].ToString();

            info.Add(new ColumnInfo
			{
				TableName = dataReader["TABLENAME"].ToString(),
				ColumnName = dataReader["COLUMNNAME"].ToString(),
				DefaultValue = dataReader["DEFAULTVALUE"].ToString().Trim('(',')'),
				TypeName = typename,
				Length = typename == "int" ? "" : dataReader["LENGTH"].ToString(),
				IsPrimaryKey = (dataReader["PRIMARYKEY"].ToString() != string.Empty && dataReader["PRIMARYKEY"].ToString() != "0"),
				IsNull = (dataReader["ISNULL"].ToString() == "Null"),
				DataScale = dataReader["DATA_SCALE"].ToString().TrimStart('0'),
				ColumnIndex = Convert.ToInt32(dataReader["COLUMNINDEX"].ToString()),
				IsForeignKey = (dataReader["FOREIGNKEY"].ToString() != string.Empty && dataReader["FOREIGNKEY"].ToString() != "0")
			});
		}
	}

	public override void FillingIndexs(List<IndexInfo>? info)
	{
		using IDataReader dataReader = ExecuteReader(" select a.name  as INDNAME,c.name as TABNAME,d.name  as COLNAMES,case indexkey_property(c.id,b.indid,b.keyno,'isdescending') \r\n \t\t                                    when 1 then 'DESC' when 0 then 'ASC' end as Sequence,INDEXPROPERTY(c.id,a.name,'IsClustered') as remark,\r\n                                            INDEXPROPERTY(c.id,a.name,'IsUnique') as IsUnique\r\n                                            from sysindexes a \r\n \t                                        join sysindexkeys b on a.id=b.id and a.indid=b.indid \r\n \t                                        join sysobjects c on b.id=c.id and c.xtype='U' and c.status>=0 \r\n \t                                        join syscolumns d on b.id=d.id and b.colid=d.colid \r\n \t                                        left join sysobjects e on b.indid=e.id and e.xtype='UQ' \r\n                                            order by c.name,a.name ");
		while (dataReader.Read())
		{
            IndexInfo indexInfo = new IndexInfo();
            indexInfo.ColumnName = dataReader["COLNAMES"].ToString();
            indexInfo.Sequence = dataReader["Sequence"].ToString();
            indexInfo.TableName = dataReader["TABNAME"].ToString();
            indexInfo.IndexName = dataReader["INDNAME"].ToString();
            indexInfo.IsUnique = dataReader["IsUnique"].ToString() != string.Empty && dataReader["IsUnique"].ToString() == "1";
            info.Add(indexInfo);
            //         int num;
            //for (num = 0; num < text.Length; num++)
            //{
            //	IndexInfo indexInfo = new IndexInfo();
            //	indexInfo.Sequence = ((text[num] == '+') ? "ASC" : "DESC");
            //	IndexInfo indexInfo2 = indexInfo;
            //	int num2 = text.IndexOf('+', num + 1);
            //	if (num2 < 0)
            //	{
            //		num2 = text.IndexOf('-', num + 1);
            //	}
            //	if (num2 > 0)
            //	{
            //		indexInfo2.ColumnName = text.Substring(num + 1, num2 - (num + 1));
            //		num = num2 - 1;
            //	}
            //	else
            //	{
            //		indexInfo2.ColumnName = text.Substring(num);
            //		num = text.Length;
            //	}
            //	indexInfo2.TableName = dataReader["TABNAME"].ToString();
            //	indexInfo2.IndexName = dataReader["INDNAME"].ToString();
            //	indexInfo2.IsUnique = dataReader["IsUnique"].ToString() != string.Empty && dataReader["IsUnique"].ToString() == "1";
            //	info.Add(indexInfo2);
            //}
        }
	}

	public override void FillingRelations(List<RelationshipInfo>? info)
	{
		using IDataReader dataReader = ExecuteReader("SELECT OBJECT_NAME(constid) as refkeyname,OBJECT_NAME(c1.id) as tabname,c1.name as colname,\r\n                                            OBJECT_NAME(c2.id) as reftabname,c2.name as refcolname,\r\n                                            ObjectProperty(f.constid,'CnstIsUpdateCascade') as CascadingUpdate,ObjectProperty(f.constid,'CnstIsDeleteCascade') as CascadingDelete \r\n                                            FROM sysforeignkeys f,syscolumns c1 ,syscolumns c2\r\n                                            where f.fkey=c1.colid and f.fkeyid = c1.id \r\n                                            and f.rkey=c2.colid and f.rkeyid = c2.id");
		while (dataReader.Read())
		{
			info.Add(new RelationshipInfo
			{
				TableName = dataReader["TABNAME"].ToString(),
				RefKeyName = dataReader["REFKEYNAME"].ToString().Trim(),
				ColumnName = dataReader["COLNAME"].ToString().Trim(),
				RelationTableName = dataReader["REFTABNAME"].ToString().Trim(),
				RelationColumnName = dataReader["REFCOLNAME"].ToString().Trim(),
				IsCascadingDelete = (dataReader["CascadingDelete"].ToString() != string.Empty && dataReader["CascadingDelete"].ToString() == "1"),
				IsCascadingUpdate = (dataReader["CascadingUpdate"].ToString() != string.Empty && dataReader["CascadingUpdate"].ToString() == "1")
			});
		}
	}

	public override void FillingViews(List<ViewInfo> info)
	{
		using IDataReader dataReader = ExecuteReader(" SELECT\r\n                      object_name(sysobjects.id) AS VIEWNAME,\r\n                      text AS CONTENT\r\n                  FROM\r\n                      sysobjects\r\n                      LEFT JOIN syscomments on syscomments.id = sysobjects.id\r\n                  WHERE\r\n                      type = N'V'\r\n                      AND permissions(sysobjects.id) & 4096 <> 0\r\n                      AND ObjectProperty(sysobjects.id, N'IsMSShipped') = 0\r\n                      AND NOT EXISTS (SELECT * FROM sys.extended_properties WHERE major_id = sysobjects.id AND name = 'microsoft_database_tools_support' AND value = 1)\r\n                  ORDER BY object_name(sysobjects.id)");
		while (dataReader.Read())
		{
			info.Add(new ViewInfo
			{
				ViewName = dataReader["VIEWNAME"].ToString(),
				Content = dataReader["CONTENT"].ToString()
			});
		}
	}

	public override void FillingProcs(List<ProcInfo> info)
	{
		using IDataReader dataReader = ExecuteReader("select name as PROCNAME,o.id as PROCID ,text as CONTENT,(select count(0) from syscolumns where id = o.id) as PARAMCOUNT from dbo.syscomments c, dbo.sysobjects o where o.id = c.id and o.type = 'p' and name not like 'sp_%' order by name");
		while (dataReader.Read())
		{
			info.Add(new ProcInfo
			{
				ProcId = dataReader["PROCID"].ToString(),
				ProcName = dataReader["PROCNAME"].ToString(),
				Content = dataReader["CONTENT"].ToString()
			});
		}
	}

	public override void FillingProcParams(List<ProcParamInfo> info)
	{
		using IDataReader dataReader = ExecuteReader("SELECT o.id as PROCID,c.name as PARMNAME,c.prec as LENGTH,b.name as TYPENAME,c.SCALE,c.colid as 'INDEX',\r\n                                                case c.isoutparam  when 0 then 'IN' when 1 then 'OUT' end STATUS\r\n                                                FROM SysColumns c,sysobjects o,systypes b \r\n                                                where c.id = o.id and  o.type = 'p' and c.xtype=b.xtype and b.status=0\r\n                                                order by o.name,c.colid");
		while (dataReader.Read())
		{
			info.Add(new ProcParamInfo
			{
				ProcId = dataReader["PROCID"].ToString(),
				ProcParamName = dataReader["PARMNAME"].ToString(),
				TypeName = dataReader["TYPENAME"].ToString(),
				Length = dataReader["LENGTH"].ToString(),
				State = dataReader["STATUS"].ToString(),
				ParamIndex = Convert.ToInt32(dataReader["INDEX"].ToString()),
				DataScale = dataReader["SCALE"].ToString()
			});
		}
	}

	public override void FillingTypeInfos(List<TypeNameInfos> info)
	{
		using IDataReader dataReader = ExecuteReader("SELECT NAME,LENGTH,XSCALE FROM SysTypes\r\n                                        where name != 'sysname'");
		while (dataReader.Read())
		{
			info.Add(new TypeNameInfos
			{
				TypeName = dataReader["NAME"].ToString(),
				Length = Convert.ToInt32(dataReader["LENGTH"].ToString()),
				Scale = Convert.ToInt32(dataReader["XSCALE"].ToString())
			});
		}
	}
}
